<?php
/**
 * Created by muniao.
 * User: muniao
 * Date: 2018/8/1
 * Time: 10:40
 */

namespace app\api\controller;


use app\api\model\AuditMaterials;
use app\api\model\Capital;
use app\api\model\PollingModel;
use think\Controller;
use think\Db;
use think\Exception;
use app\index\model\Jpush;
use think\Request;
use app\api\model\Common;
use phpcvs\Csv;
use app\index\model\Pdf;
use app\api\model\OrderModel;
use function GuzzleHttp\Psr7\str;
use PhpOffice\PhpWord\PhpWord;
use PhpOffice\PhpWord\IOFactory;

class Tongji extends Controller
{
    
   
    
    public function weixin()
    {
        $xlsCell = ['姓名', '加微信', '未加微信'];
        $entry   = db('order', config('database.zong'))->field('entry')->group('entry')->column('entry');
        foreach ($entry as $k => $imt) {
            $state                      = db('order', config('database.zong'))->where('entry', $imt)->where('wechat', 0)->count();
            $states                     = db('order', config('database.zong'))->where('entry', $imt)->where('wechat', 1)->count();
            $ues[$k]['entry']           = $imt;
            $ues[$k]['channel_details'] = $states;
            $ues[$k]['channel_id']      = $state;
        }
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($ues, $xlsCell);
        return res_date($ues);
    }
    
    function get_distance($from, $to, $km = true, $decimal = 2)
    {
        sort($from);
        sort($to);
        $EARTH_RADIUS = 6370.996; // 地球半径系数
        
        $distance = $EARTH_RADIUS * 2 * asin(sqrt(pow(sin(($from[0] * pi() / 180 - $to[0] * pi() / 180) / 2), 2) + cos($from[0] * pi() / 180) * cos($to[0] * pi() / 180) * pow(sin(($from[1] * pi() / 180 - $to[1] * pi() / 180) / 2), 2))) * 1000;
        
        if ($km) {
            $distance = $distance / 1000;
        }
        
        return round($distance, $decimal);
    }
    
    
    public function tui()
    {
        
        $param     = Request::instance()->get();
        $xlsCell   = ['时间', '城市', '推客类别', '物业', '社区名称', '推单量', '有效单量(上门)', '成交量', '成交额', '上门奖金', '成交奖金'];
        $capital   = db('capital')
            ->field('sum(to_price) as to_price,ordesr_id')
            ->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])
            ->group('ordesr_id')
            ->buildSql();
        $envelopes = db('envelopes')
            ->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,ordesr_id')
            ->where(['envelopes.type' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $income    = db('income')
            ->field('sum(income.commission) as commission,sum(income.deal) as deal,order_id')
            ->where(['income.state' => 1])
            ->group('order_id')
            ->buildSql();
        $clock_in  = db('clock_in')
            ->field('count(id) as count,order_id')
            ->group('order_id')
            ->buildSql();
        $list      = db('personal')
            ->join('order', 'order.tui_jian=personal.personal_id', 'left')
            ->join('personal personals', 'personals.personal_id=personal.num', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join([$income => 'income'], 'order.order_id=income.order_id ', 'left')
            ->join([$clock_in => 'clock_in'], 'order.order_id=clock_in.order_id', 'left')
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$capital => 'capital'], 'capital.ordesr_id=order.order_id', 'left')
            ->join('user', 'user.user_id = order.assignor', 'left')
            ->join('city', 'city.city_id = order.city_id', 'left');;
        
        $data = $list->where('user.ce', 'neq', 2)->where('order.created_time', '>', 1609430400)->field('FROM_UNIXTIME(order.created_time,"%Y年%m月%d %H:%i:%S") as created_time,city.city,if(personal.num !=0,"物业推客","个人推客") as tui,group_community.username,group_community1.username as username1,count(distinct order.order_id) as quan,clock_in.count as you,count(distinct CASE WHEN order.state<=7 and order.state>3  THEN order.order_id ELSE NULL END) as count,concat(capital.to_price + envelopes.expense-envelopes.give_money) as sum,income.commission,income.deal')->group('FROM_UNIXTIME(order.created_time,"%Y-%m"),city.city,group_community.username,group_community1.username')->order('created_time desc')->select();
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($data, $xlsCell);
        
        
    }
    
    public function tuige()
    {
        $xlsCell = ['推客类别', '推客姓名', '推单量', '有效单量(上门)', '成交量', '成交额', '上门奖金', '成交奖金'];
        
        $list = db('personal')
            ->join('order', 'order.tui_jian=personal.personal_id', 'left')
            ->join('clock_in', 'order.order_id=clock_in.order_id', 'left')
            ->join('user', 'user.user_id = order.assignor', 'left');
        
        $data = $list->where('user.ce', 'neq', 2)->where('order.created_time', '>', 1609430400)->field('if(personal.num !=0,"物业推客","个人推客") as tui,personal.username,count(distinct order.order_id) as quan,count(distinct clock_in.order_id) as you,count(distinct CASE WHEN order.state<=7 and order.state>3  THEN order.order_id ELSE NULL END) as count,personal.personal_id')->group('personal.personal_id')->order('quan desc')->select();
        
        foreach ($data as $k => $item) {
            $order_id = db('order')->where('tui_jian', $item['personal_id'])->column('order_id');
            $sum      = db('capital')
                ->where('capital.types', 1)->where('capital.enable', 1)->where('capital.agency', 0)
                ->whereIn('capital.ordesr_id', $order_id)->sum('to_price');
            
            $envelopes = db('envelopes')->whereIn('envelopes.ordesr_id', $order_id)->where('type', 1)->field('ifnull(sum(envelopes.give_money),0) as give_money, ifnull(sum(envelopes.expense),0) as expense')->select();
            
            $income                 = db('income')->whereIn('income.order_id', $order_id)->where('state', 1)->field('sum(commission) as commission,sum(deal) as deal')->select();
            $data[$k]['sum']        = $sum - $envelopes[0]['give_money'] + $envelopes[0]['expense'];
            $data[$k]['commission'] = $income[0]['commission'];
            $data[$k]['deal']       = $income[0]['deal'];
            unset($data[$k]['personal_id']);
        }
//        $last_names = array_column($list, 'count');
//        array_multisort($last_names, SORT_DESC, $list);
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($data, $xlsCell);
        
        
    }
    
    
    function mFristAndLast($y = "", $m = "")
    {
        if ($y == "") $y = date("Y");
        if ($m == "") $m = date("m");
        $m = sprintf("%02d", intval($m));
        $y = str_pad(intval($y), 4, "0", STR_PAD_RIGHT);
        
        $m > 12 || $m < 1 ? $m = 1 : $m = $m;
        $firstday    = strtotime($y . $m . "01000000");
        $firstdaystr = date("Y-m-01", $firstday);
        $lastday     = strtotime(date('Y-m-d 23:59:59', strtotime("$firstdaystr +1 month -1 day")));
        
        return array(
            "firstday" => $firstday,
            "lastday" => $lastday
        );
    }
    
    public function tui1()
    {
        
        $param     = Request::instance()->get();
        $xlsCell   = ['时间', '城市', '物业', '社区名称', '推单量', '预约上门', '打卡上门', '远程沟通量', '成交量', '成交额'];
        $id        = db('community')->column('id');
        $order_id  = db('order')->where('tui_jian', '<>', 0)->column('order_id');
        $order_id  = array_merge($id, $order_id);
        $capital   = db('capital')
            ->field('sum(to_price) as to_price,ordesr_id')
            ->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])
            ->group('ordesr_id')
            ->buildSql();
        $envelopes = db('envelopes')
            ->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,ordesr_id')
            ->where(['envelopes.type' => 1])
            ->group('ordesr_id')
            ->buildSql();
        
        $clock_in = db('clock_in')
            ->field('count(id) as count,order_id')
            ->group('order_id')
            ->buildSql();
        $through  = db('through')
            ->field('count(through_id) as count,order_ids')
            ->where('role', 2)
            ->group('order_ids')
            ->buildSql();
        $list     = db('order')
            ->join('personal', 'order.tui_jian=personal.personal_id', 'left')
            ->join('community', 'order.order_id=community.id', 'left')
            ->join('personal personals', 'personals.personal_id=personal.num', 'left')
            ->join('group_community group_community', 'personals.group_id=group_community.group_id', 'left')
            ->join('group_community group_community1', 'personal.residential_quarters=group_community1.group_id', 'left')
            ->join([$clock_in => 'clock_in'], 'order.order_id=clock_in.order_id', 'left')
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$through => 'through'], 'through.order_ids=order.order_id', 'left')
            ->join([$capital => 'capital'], 'capital.ordesr_id=order.order_id', 'left')
            ->join('user', 'user.user_id = order.assignor', 'left')
            ->join('residential', 'residential.residential_id=community.residential_id', 'left')
            ->join('city', 'city.city_id = order.city_id', 'left');;
        
        $data = $list->where('user.ce', 'neq', 2)->whereIn('order.order_id', $order_id)->where('order.created_time', '>', 1609430400)->field('FROM_UNIXTIME(order.created_time,"%Y年%m月%d %H:%i:%S") as created_time,city.city,if(order.tui_jian !=0,group_community.username,residential.content) as content,if(order.tui_jian !=0,group_community1.username,order.residential_quarters) as residential_quarters,count(distinct order.order_id) as quan,count(distinct CASE WHEN order.planned !=0 THEN order.order_id ELSE NULL END) as shang,clock_in.count as you,through.count as yuan,count(distinct CASE WHEN order.state<=7 and order.state>3  THEN order.order_id ELSE NULL END) as cheng,concat(capital.to_price + envelopes.expense-envelopes.give_money) as sum')->group('FROM_UNIXTIME(order.created_time,"%Y-%m"),city.city,group_community.username,group_community1.username')->order('created_time desc')->select();
        
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($data, $xlsCell);
        
        
    }
    
    /*
     * 推荐列表
     */
    public function recommendation()
    {
        
        $capital   = db('capital')
            ->field('sum(to_price) as to_price,ordesr_id')
            ->where(['capital.types' => 1, 'capital.enable' => 1, 'capital.agency' => 0])
            ->group('ordesr_id')
            ->buildSql();
        $envelopes = db('envelopes')
            ->field('sum(envelopes.give_money) as give_money,sum(envelopes.expense) as expense,ordesr_id')
            ->where(['envelopes.type' => 1])
            ->group('ordesr_id')
            ->buildSql();
        $clock_in  = db('clock_in')
            ->field('count(id) as count,order_id')
            ->group('order_id')
            ->buildSql();
        $payment   = db('payment')
            ->field('count(payment_id) as count,orders_id')
            ->where(function ($quer) {
                $quer->where(
                    function ($quer) {
                        $quer->where(['weixin' => ['<>', 2], 'success' => 1]);
                    })->whereOr(function ($quer) {
                    $quer->where(['weixin' => 2, 'success' => 2]);
                });
            })
            ->group('orders_id')
            ->buildSql();
        
        $personal_share = \db('personal_share')
            ->join('personal', 'personal_share.references=personal.personal_id', 'left')
            ->join('personal personal1', 'personal_share.register=personal1.personal_id', 'left')
            ->join('order', 'order.tui_jian=personal1.personal_id', 'left')
            ->join('city', 'city.city_id = order.city_id', 'left')
            ->join([$clock_in => 'clock_in'], 'order.order_id=clock_in.order_id', 'left')
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$capital => 'capital'], 'capital.ordesr_id=order.order_id', 'left')
            ->join([$payment => 'payment'], 'payment.orders_id=order.order_id', 'left')
            ->field('city.city,personal.username,personal.mobile,personal1.username as username1,personal1.mobile as mobile1,order.order_no,if(order.state !=10,"是","否") as you,if(clock_in.count !=0,"是","否") as shang,if(payment.count !=0,"是","否") as fu,concat(capital.to_price + envelopes.expense-envelopes.give_money) as jin,(round((capital.to_price + envelopes.expense-envelopes.give_money)*0.01)) as ti')
            ->select();
        $xlsCell        = ['邀请人姓名', '邀请人手机号', '被邀请人手机号', '推荐订单号', '是否有效订单', '是否上门', '打卡上门', '是否付款', '签约金额', '提成金额'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($personal_share, $xlsCell);
        
    }
    
    public function Ranking()
    {
        $date            = \request()->get();
        $startTime       = strtotime(date('Y-m-01 H:i:s', strtotime(date('Y-m-d'))));
        $endTime         = strtotime(date('Y-m-t H:i:s', strtotime(date('Y-m-d') . "23:59:59")));
        $group_id        = db('group_community')->where('group_id', '=', $date['group_id'])->value('group_id');
        $group_community = db('group_community')->where('pid', '=', $group_id)->column('group_id');
        
        $list    = db('personal')
            ->Join('income', 'income.personal_id=personal.personal_id and `income`.`uptime` BETWEEN ' . $startTime . ' AND ' . $endTime, 'left')
            ->whereIn('personal.residential_quarters', $group_community)
            ->where('personal.num', '<>', 0)
            ->Join(config('database.connections.zong')['database'] . '.personal personals', 'personals.personal_id=personal.num', 'left')
            ->Join(config('database.connections.zong')['database'] . '.group_community', 'group_community.group_id=personals.group_id', 'left')
            ->Join(config('database.connections.zong')['database'] . '.group_community group_communitys', 'group_communitys.group_id=personal.residential_quarters', 'left')
            ->field('personal.username,sum(income.profit) as sum,group_community.username as username1,group_communitys.username as user')
            ->group('personal.personal_id')
            ->order('sum desc,personal.personal_id desc')
            ->select();
        $xlsCell = ['推客宝姓名', '收益', '物业', '所属小区'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($list, $xlsCell);
    }
    
    
    public function direct_order()
    {
        
        
        $bj = db('direct_order', config('database.zong'))
            ->Join('order_info', 'order_info.order_id=direct_order.order_id', 'left')
            ->Join('order', 'order.order_id=direct_order.order_id', 'left')
            ->Join('direct_payment_record', 'direct_payment_record.serial_number=direct_order.payNo', 'left')
            ->field('order_info.store_name,order_info.assignor,order.order_no,FROM_UNIXTIME(direct_order.distribute_time,"%Y年%m月%d %H:%i:%S") as distribute_time,FROM_UNIXTIME(direct_order.complete_time,"%Y年%m月%d %H:%i:%S") as complete_time,direct_order.type,direct_order.user_id,direct_order.payNo,direct_order.order_id,direct_payment_record.price,direct_payment_record.mode')
            ->select();
        foreach ($bj as $k => $item) {
            if (substr($item['order_id'], 0, 3) == 241) {
                
                $content['city']      = 'db2';
                $content['shopowner'] = 'cd';
            } elseif (substr($item['order_id'], 0, 3) == 239) {
                $content['shopowner'] = 'cq';
                $content['city']      = 'cq';
            } elseif (substr($item['order_id'], 0, 3) == 172) {
                $content['shopowner'] = 'wh';
                $content['city']      = 'wh';
            } elseif (substr($item['order_id'], 0, 3) == 262) {
                $content['shopowner'] = 'gy';
                $content['city']      = 'gy_mast';
            } elseif (substr($item['order_id'], 0, 3) == 375) {
                $content['shopowner'] = 'sh';
                $content['city']      = 'sh';
            } elseif (substr($item['order_id'], 0, 3) == 202) {
                $content['shopowner'] = 'sz';
                $content['city']      = 'sz';
            }
            if ($item['type'] == 1) {
                if ($content['shopowner'] == 'cd') {
                    $username = db('user')->where('user_id', $item['user_id'])->value('username');
                } else {
                    $username = db('user', config('database.' . $content['shopowner'] . ''))->where('user_id', $item['user_id'])->value('username');
                }
                
            } else {
                $username = db('app_user', config('database.' . $content['city'] . ''))->where('id', $item['user_id'])->value('username');
                
            }
            if ($item['type'] == 1) {
                $type = "店长";
            } else {
                $type = "师傅";
            }
            if ($item['mode'] == 1) {
                $mode = "支付宝";
            } else {
                $mode = "微信";
            }
            $data[$k]['store_name']      = $item['store_name'];
            $data[$k]['assignor']        = $item['assignor'];
            $data[$k]['order_no']        = $item['order_no'] . "\t";
            $data[$k]['payNo']           = $item['payNo'];
            $data[$k]['distribute_time'] = $item['distribute_time'];
            $data[$k]['complete_time']   = $item['complete_time'];
            $data[$k]['type']            = $type;
            $data[$k]['username']        = $username;
            $data[$k]['price']           = $item['price'];
            $data[$k]['mode']            = $mode;
        }
        
        $xlsCell = ['店铺', '店长', '订单号', '流水号', '派单时间', '完成时间', '店长/师傅', '操作人', '金额', '支付方式'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($data, $xlsCell);
    }
    
    
    public function orderThrough()
    {
        $dataList  = [];
        $envelopes = db('envelopes', config('database.zong'))
            ->field('count(envelopes.envelopes_id) as envelopesCount,envelopes.ordesr_id')
            ->group('ordesr_id')
            ->buildSql();
        $through   = db('through', config('database.zong'))
            ->field('sum(if(role=1,1,0)) as customer,sum(if(role=2,1,0)) as user,through.order_ids')
            ->group('order_ids')
            ->buildSql();
        $order     = db('order', config('database.zong'))
            ->join('order_info', 'order_info.order_id=order.order_id', 'left')
            ->join([$envelopes => 'envelopes'], 'envelopes.ordesr_id=order.order_id', 'left')
            ->join([$through => 'through'], 'through.order_ids=order.order_id', 'left')
            ->field('order.order_id,concat(order_info.city,order_info.county,order.addres) as addres,order.contacts,order.telephone,case order.visiting_time when 1 then "立即" when 2 then "三天内" when 3 then "七天内" else "不着急" end as visiting,order_info.pro_id_title,FROM_UNIXTIME(order.created_time,"%Y年%m月%d %H:%i:%S") as created_time,order_info.assignor,order_info.channel_title,envelopes.envelopesCount,through.customer,through.user,if(order.state>3 and order.state<=7,"已签约","未签约") as qian')
            ->whereIn('order.channel_id', [33, 24, 40, 41, 11])
            ->group('order.order_id')
            ->select();
        
        $xlsCell = ['订单id', '维修地址', '客户名称', '联系电话', '期望上门时间', '房屋问题', '创建时间', '店长', '渠道', '报价次数', '店长跟单次数', '客户跟单次数', '是否签约'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($order, $xlsCell);
    }
    
    
    public function node()
    {
        $startTimestamp = strtotime(date('Y-m-01 00:00:00', strtotime('-5 months')));
        $endTimestamp   = time();
        
        $list = [];
        // 循环输出每一天的日期
        $j = 0;
        for ($i = $startTimestamp; $i <= $endTimestamp; $i += 86400) {
            $date  = date('Y-m-d', $i);
            $stare = date('Y-m-d 00:00:00', $i);
            $end   = date('Y-m-d 23:59:59', $i);
            $stare = strtotime($stare);
            $end   = strtotime($end);
            $sql   = "SELECT
	COUNT( auxiliary_delivery_node.adn_id ) AS count
FROM
	`auxiliary_delivery_node`
	LEFT JOIN `auxiliary_delivery_schedule` ON `auxiliary_delivery_schedule`.`id` = `auxiliary_delivery_node`.`auxiliary_delivery_schedul_id`
	LEFT JOIN `auxiliary_project_list` ON `auxiliary_project_list`.`id` = `auxiliary_delivery_schedule`.`auxiliary_project_list_id`
WHERE
	`auxiliary_project_list`.`delete_time` IS NULL
	AND `auxiliary_delivery_schedule`.`delete_time` IS NULL
	AND  auxiliary_delivery_node.upload_time BETWEEN " . $stare . " and " . $end;
//            echo $sql . "\n";;
            //原生sql
            $reality_artificial = db('auxiliary_delivery_node');
            $sum                = $reality_artificial->query($sql);
            
            $sql1 = "SELECT
	COUNT( auxiliary_delivery_node.audit_time ) AS count
FROM
	`auxiliary_delivery_node`
	LEFT JOIN `auxiliary_delivery_schedule` ON `auxiliary_delivery_schedule`.`id` = `auxiliary_delivery_node`.`auxiliary_delivery_schedul_id`
	LEFT JOIN `auxiliary_project_list` ON `auxiliary_project_list`.`id` = `auxiliary_delivery_schedule`.`auxiliary_project_list_id`
WHERE
	`auxiliary_project_list`.`delete_time` IS NULL
	AND `auxiliary_delivery_schedule`.`delete_time` IS NULL
	AND  auxiliary_delivery_node.audit_time BETWEEN " . $stare . " and " . $end;
//            echo $sql . "\n";;
            //原生sql
            $reality_artificial = db('auxiliary_delivery_node');
            $sum1               = $reality_artificial->query($sql1);
            
            
            $stare1 = $stare - (3 * 24 * 60 * 60);
            $sql2   = "SELECT
COUNT( auxiliary_delivery_node.audit_time ) AS count
FROM
 `auxiliary_delivery_node`
 LEFT JOIN `auxiliary_delivery_schedule` ON `auxiliary_delivery_schedule`.`id` = `auxiliary_delivery_node`.`auxiliary_delivery_schedul_id`
 LEFT JOIN `auxiliary_project_list` ON `auxiliary_project_list`.`id` = `auxiliary_delivery_schedule`.`auxiliary_project_list_id`
WHERE
 `auxiliary_delivery_schedule`.`delete_time` IS NULL
 AND `auxiliary_project_list`.`delete_time` IS NULL
 AND `auxiliary_delivery_node`.`audit_time` BETWEEN " . $stare . " and " . $end . "
	AND  auxiliary_delivery_node.upload_time BETWEEN " . $stare1 . " and " . $end;
//            echo $sql . "\n";;
            //原生sql
            $reality_artificial = db('auxiliary_delivery_node');
            $sum2               = $reality_artificial->query($sql2);
            $list[$j]['date']   = $date;
            $list[$j]['count']  = $sum[0]['count'];
            $list[$j]['count1'] = $sum1[0]['count'];
            $list[$j]['count2'] = $sum2[0]['count'];
            $j++;
            
        }
        $list    = array_merge($list);
        $xlsCell = ['日期', '当月每天提交巡检节点的数量', '当月每天验收通过巡检节点的数量', '当月每天验收通过巡检节点的数量(通过的节点最后提交日期为通过当日前3日内)'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($list, $xlsCell);
    }
    
    public function auditMaterialsList()
    {
        $date = \request()->get();
        if (empty($date['time'])) {
            echo "请输入时间";
            die;
        }
        $timestamp          = strtotime($date['time']);
        $startTime          = strtotime(date('Y-m-1 00:00:00', $timestamp));
        $endTime            = strtotime($date['endTime']);
        $endTime            = strtotime(date('Y-m-d 23:59:59', $endTime));
        $auditMaterialsList = \db('audit_materials')
            ->field('order.order_no,capital.class_b,FROM_UNIXTIME(order.settlement_time,"%Y-%m-%d %H:%i:%s")as settlementTime,store.store_name,user.username,FROM_UNIXTIME(audit_materials.creationtime,"%Y-%m-%d %H:%i:%s")as createdTime,audit_materials.capital_id,materials_id,order.city_id')
            ->join('capital', 'capital.capital_id=audit_materials.capital_id', 'left')
            ->join('order', 'capital.ordesr_id=order.order_id', 'left')
            ->join('user', 'user.user_id=audit_materials.user_id', 'left')
            ->join('store', 'user.store_id=audit_materials.store_id', 'left')
            ->where('capital.types', 1)
            ->where('capital.enable', 1)
            ->where('order.settlement_time', '<>', 0)
            ->whereNotNull('order.settlement_time')
            ->group('capital.capital_id')
            ->order('audit_materials.materials_id desc')
            ->whereBetween('audit_materials.creationtime', [$startTime, $endTime])
            ->select();
        
        $description     = \db('user_config', config('database.zong'))->where('key', 'audit_materials_evaluate')->value('value');
        $description     = json_decode($description, true);
        $descriptionList = $description;
        foreach ($auditMaterialsList as $s => $v1) {
            $list         = \db('audit_materials_evaluate')->where('materials_id', $v1['materials_id'])->select();
            $descriptions = $descriptionList;
            $lo           = [];
            foreach ($descriptions as $k => $item) {
                $l = '';
                foreach ($item['choice'] as $k1 => $value) {
                    $descriptions[$k]['choice'][$k1]['select'] = 0;
                    foreach ($list as $v) {
                        if ($item['type'] == $v['type'] && $value['id'] == $v['evaluate']) {
                            $l = $value['title'];
                        }
                    }
                }
                $lo[] = $l;
            }
            
            $auditMaterialsList[$s]['agency_name'] = \db('reimbursement')->join('reimbursement_relation', 'reimbursement_relation.reimbursement_id=reimbursement.id', 'left')->join('agency', 'agency.agency_id=reimbursement_relation.bank_id', 'left')->where('find_in_set(:id,reimbursement.capital_id)', ['id' => $v1['capital_id']])->value('agency.agency_name');
            
            $auditMaterialsList[$s]['description']  = empty($lo) ? '' : $lo[0];
            $auditMaterialsList[$s]['description1'] = empty($lo) ? '' : $lo[1];
            $auditMaterialsList[$s]['description2'] = empty($lo) ? '' : $lo[2];
            $auditMaterialsList[$s]['description3'] = empty($lo) ? '' : $lo[3];
            $auditMaterialsList[$s]['description4'] = empty($lo) ? '' : $lo[4];
            unset($auditMaterialsList[$s]['capital_id'], $auditMaterialsList[$s]['materials_id']);
        }
        $xlsCell = ['订单号', '主材名称', '订单结算时间', '店铺', '店长名称', '主材验收完成时间', '城市', '供应商名称', '质量评价', '时效评价', '供应商服务评价', '客户满意度评价', '内部满意度评价'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($auditMaterialsList, $xlsCell);
    }
    
    /*
    * 成都所有店铺订单
    */
    public function beExpectedTo()
    {
        $param      = Request::instance()->get();
        $Capital    = new Capital();
        $arr        = date_parse_from_format('Y年m月', $param['startTime']);
        $time       = mktime(0, 0, 0, $arr['month'], 01, $arr['year']);
        $start      = strtotime(date('Y-m-01 00:00:00', $time));//获取指定月份的第一天
        $end        = strtotime(date('Y-m-t 23:59:59', $time)); //获取指定月份的最后一天
        $order      = \db('order')
            ->field('order.city_id,envelopes.give_money,envelopes.expense,envelopes.purchasing_expense,envelopes.purchasing_discount,
             order_aggregate.main_price-order_setting.order_fee- order_aggregate.material_price-order_aggregate.reimbursement_price-order_aggregate.master_price as lirun, order_aggregate.main_price,
           order.order_id as orderId,order.state,order.created_time,user.store_id,envelopes.envelopes_id,order_aggregate.total_price,order_aggregate.main_price,order_aggregate.agent_price,order.order_no')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->join('envelopes', 'envelopes.ordesr_id=order.order_id and envelopes.type=1', 'left')
            ->join('order_aggregate', 'order.order_id=order_aggregate.order_id', 'left')
            ->join('order_setting', 'order.order_id=order_setting.order_id', 'left');
        $orderList  = $order->whereBetween('order.created_time', [$start, $end])
//            $order->where(function ($quer) use ($start, $end) {
//            $quer->whereBetween('order.cleared_time', [$start, $end])->whereOr('contract.con_time', 'between', [$start, $end]);
//        })
            
            ->where('order_aggregate.total_price', '>',0)->where('order.state', '<',4)->where('user.ce', '<>', 2)->group('order.order_id')->select();
        $orderArray = [];
        foreach ($orderList as $k => $item) {
            $orderArray[$k]['id']               = $item['orderId'];
            $orderArray[$k]['order_no']               = $item['order_no'];
            $orderArray[$k]['total_price']            = $item['total_price'];
            $orderArray[$k]['main_price']            = $item['main_price'];
            $orderArray[$k]['agent_price']            = $item['agent_price'];
            $orderArray[$k]['give_money']            = $item['give_money'];
            $orderArray[$k]['expense']            = $item['expense'];
            $orderArray[$k]['purchasing_expense']            = $item['purchasing_expense'];
            $orderArray[$k]['purchasing_discount']            = $item['purchasing_discount'];
            $orderArray[$k]['state']            = $item['state'];
            $orderArray[$k]['created_time']            = date('Y-m-d H:i:s',$item['created_time']);
            $orderArray[$k]['clearedOrde']      = 0;
            $orderArray[$k]['beExpectedToOrde'] = 0;
            if (!empty($item['cleared_time'])) {
                $orderArray[$k]['clearedOrde'] = round($item['lirun'] / $item['main_price'], 4);
            }
            $l                 = $Capital->capitalCheck($item['orderId'], $item['envelopes_id']);
            $u8c_invprice      = db('u8c_invprice', config('database.zong'))->where('store_id', $item['store_id'])->select();
            $labor_cost_config = db('labor_cost_config', config('database.zong'))->where('city_id', $item['city_id'])->find();
            foreach ($l as $k1 => $v) {
                $zhuMaterialCost = 0;
                if (!empty($v['content'])) {
                    $content = json_decode($v['content'], true);
                    foreach ($content as $k1 => $item) {
                        foreach ($u8c_invprice as $value) {
                            if ($item['invcode'] == $value['invcode'] && $item['sum'] == 0) {
                                $content[$k]['sum'] = bcmul(bcmul($item['recommended_quantity'], $value['nabprice'], 2), $v['square'], 2);
                                $zhuMaterialCost    += $content[$k1]['sum'];
                                
                            }
                        }
                        
                    }
                    
                    
                    $v['content'] = json_encode($content);
                }
                
                $zhu           = array_sum(array_column($l, 'main_price')) - $l[0]['give_money'] + $l[0]['expense'];//祝主合同清单
                $zhuLaborCosts = array_sum(array_column($l, 'zhuLaborCost'));
                
                $zhuLaborCost  = $zhuLaborCosts + $zhu * ($labor_cost_config['fixed_labor_cost_ratio'] / 100);//人工费
                $zhuMeasurefee = bcmul($zhu, ($labor_cost_config['main_contract_measure_ratio'] / 100), 2);//措施费
                
                $zhuOrderFee = $l[0]['order_fee'];//订单费
                $zhuProfit   = 0;
                if ($zhu != 0) {
                    $zhuProfit = bcsub(bcsub(bcsub(bcsub($zhu, $zhuLaborCosts, 4), $zhuMeasurefee, 4), $zhuMaterialCost, 4), $zhuOrderFee, 4);
                    $zhuProfit = bcsub(bcmul(sprintf('%.4f', $zhuProfit / $zhu), 100, 4), $labor_cost_config['fixed_labor_cost_ratio'], 3);//利润
                }
                $dai             = array_sum(array_column($l, 'agent_price')) - $l[0]['purchasing_discount'] + $l[0]['purchasing_expense'];
                $daiMeasurefee   = bcmul($dai, ($labor_cost_config['agency_contract_measure_ratio'] / 100), 2);
                $daiLaborCost    = array_sum(array_column($l, 'daiLaborCost'));
                $daiOrderFee     = $l[0]['order_fee'];
                $daiMaterialCost = array_sum(array_column($l, 'daiMaterialCost'));//材料费
                
                if ($zhu != 0) {
                    $daiOrderFee = 0;
                }
                $daiProfit = 0;
                
                if ($dai != 0) {
                    $estimate=round(($daiLaborCost+$daiMeasurefee+$daiMaterialCost)/$dai,4)*100;
                    if($estimate<60){
                        $daiMaterialCost=array_sum(array_column($l, 'agent_price'))*0.6;
                    }
                    $daiProfit = bcsub(bcsub(bcsub(bcsub($dai, $daiLaborCost, 4), $daiMeasurefee, 4), $daiMaterialCost, 4), $daiOrderFee, 4);
                    $daiProfit = bcmul(sprintf('%.4f', $daiProfit / $dai), 100, 4);//利润
                }
//                $orderArray[$k]['beExpectedToOrde'] = (string)$zhu;//利润
                $orderArray[$k]['beExpectedToOrde'] = $zhuProfit;//利润
                $orderArray[$k]['beExpectedToOrde1'] = $daiProfit;//利润
            }
            
            
        }
        cache('主合同利润', $orderArray);
        $xlsCell = ['订单ID','订单编号','订单金额', '主合同金额','代购合同金额', '主合同优惠金额', '代购合同优惠金额','主合同管理费','代购合同管理费', '订单状态','创建时间', '订单实际主合同利润', '订单预估主合同利润','订单预估代购合同利润'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($orderArray, $xlsCell);
    }
    
    public function app_user_order_capital_labor_costs()
    {
        $order   = \db('app_user_order_capital_labor_costs')
            ->field('FROM_UNIXTIME(adopt_time,"%Y-%m-%d")as adopt_time,sum(if((state =1 || state =0) and examine_user_id !=0,1,0)) as "第三人审核通过数",sum(if(state=2 and examine_user_id !=0,1,0)) as "第三人审核不通过数"')
            ->group('app_user_order_capital_labor_costs.adopt_time')->select();
        $xlsCell = ['审核时间', '第三人审核通过数', '不通过数'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($order, $xlsCell);
    }
    
    public function masterDuan()
    {
        $param = Request::instance()->get();
        $id = base64_decode(strtr($param['i'], '-_', '+/'));
        $key128 = '0123456789abcdef';
        $iv = 'abcdef0123456789';
        $param['id'] = openssl_decrypt($id, 'aes-128-cbc', $key128, OPENSSL_RAW_DATA, $iv);
        $user  = [];
        //你入职益鸟已经X天了（注解：按照系统内入职时间计算，部分人可能和实际有差异）
        //在过去的2023年，你在X店铺，与众多师傅店长一起努力，感谢你，祝2024，工作顺利！
        $days_diff         = \db('app_user')
            ->field('TIMESTAMPDIFF(DAY, FROM_UNIXTIME(created_at,"%Y-%m-%d %H:%i:%s"),NOW()) AS days_diff,store.store_name')
            ->join('store', 'app_user.store_id=store.store_id', 'left')
            ->where('app_user.id', $param['id'])->find();
        if(empty($days_diff)){
            res_date(null,300,'分享链接参数错误，无法查看，祝您新年新气象');
        }
        $user['entry']     = empty($days_diff) ? 0 : $days_diff['days_diff'];
        $user['storeName'] = empty($days_diff) ? '' : $days_diff['store_name'];
        //过去一年，你参与施工了X个订单，其中【局部改造】最多，达到X个（注解：数据中排除了0工费的工地）
        $app_user_order_capital = db('app_user_order_capital')
            ->field('order_id,user_id')
            ->distinct(true)
            ->where('app_user_order_capital.work_time', '<>', 0)
            ->whereNull('app_user_order_capital.deleted_at')
            ->group('order_id')
            ->buildSql();
        $orderProId             = db('order')
            ->field('count(order.pro_id) as value,goods_category.title as name')
            ->join('app_user_order', 'order.order_id=app_user_order.order_id', 'left')
            ->join('goods_category', 'order.pro_id=goods_category.id', 'left')
//            ->join('app_user_order_capital', 'app_user_order_capital.user_id=app_user_order.user_id and app_user_order_capital.order_id=app_user_order.order_id and app_user_order_capital.deleted_at is null', 'left')
            ->join([$app_user_order_capital => 'app_user_order_capital'], 'app_user_order_capital.order_id=app_user_order.order_id and app_user_order_capital.user_id=' . $param['id'], 'right')
            ->where('app_user_order.status', '<>', 10)
            ->where('app_user_order.created_at', '>=', 1672502400)
            ->whereNull('app_user_order.deleted_at')
            ->where('app_user_order.user_id', $param['id'])
            ->group('order.pro_id')
            ->select();

//        $user['participateInConstruction'] = empty($orderProId) ? '' : $orderProId[array_search(max(array_column($orderProId, 'count')), array_column($orderProId, 'count'))];
        $user['participateInConstruction'] = ['participateInConstruction'=> empty($orderProId) ? 0 :array_sum(array_column($orderProId, 'value')),'max'=> empty($orderProId) ? ["value"=>0, "name"=>""] :$orderProId[array_search(max(array_column($orderProId, 'value')), array_column($orderProId, 'count'))],'list'=> empty($orderProId) ? [] :$orderProId];
        //3、过去一年，你完工结算了X个订单，
        //和X店长配合的最多（注解：数据中排除了0工费的工地）
        $orderCoordinate = db('order')
            ->field('count(order.assignor) count,user.username')
            ->join('app_user_order', 'order.order_id=app_user_order.order_id', 'left')
            ->join('user', 'order.assignor=user.user_id', 'left')
            ->join([$app_user_order_capital => 'app_user_order_capital'], 'app_user_order_capital.order_id=app_user_order.order_id and app_user_order_capital.user_id=' . $param['id'], 'right')
            ->where('app_user_order.status', '<>', 10)
            ->where('app_user_order.created_at', '>=', 1672502400)
            ->whereNull('app_user_order.deleted_at')
            ->where('app_user_order.user_id', $param['id'])
            ->group('order.assignor')
            ->select();
        
        //6、还记得吗，在X工地，你获得了X工费，是今年最大的一笔单工地收入
        
        $app_user_order_capitals = db('app_user_order_capital')
            ->field('order_id,user_id,sum(personal_price) as personal_price')
            ->distinct(true)
            ->where('app_user_order_capital.work_time', '<>', 0)
            ->whereNull('app_user_order_capital.deleted_at')
            ->group('order_id,user_id')
            ->buildSql();
        $orderPrice              = db('app_user_order')
            ->field('sum(app_user_order_capital.personal_price) as master_price,app_user_order.order_id,order.addres')
            ->join([$app_user_order_capitals => 'app_user_order_capital'], 'app_user_order_capital.order_id=app_user_order.order_id and app_user_order_capital.user_id=' . $param['id'], 'right')
            ->join('order', 'order.order_id=app_user_order.order_id', 'left')
            ->where('app_user_order.status', 5)
            ->where('app_user_order.cleared_time', '>=', 1672502400)
            ->whereNull('app_user_order.deleted_at')
            ->group('app_user_order.order_id')
            ->where('app_user_order.user_id', $param['id'])
            ->select();
        $user['orderCoordinate'] =['count'=>0,'coordinate'=>''];
        
        if (!empty($orderCoordinate)) {
            $user['orderCoordinate'] = ['count' => count($orderPrice), 'coordinate' => $orderCoordinate[array_search(max(array_column($orderCoordinate, 'count')), array_column($orderCoordinate, 'count'))]['username']];
        }
        $user['orderPrice'] = ['price'=>0,'orderPriceAddres'=>''];
        if (!empty($orderPrice)) {
            $maxArray           = $orderPrice[array_search(max(array_column($orderPrice, 'master_price')), array_column($orderPrice, 'master_price'))];
            $user['orderPrice'] = ['price' => $maxArray['master_price'], 'orderPriceAddres' => $maxArray['addres']];
        }
        //4、还记得吗，你最早的一次工地打卡，是X点，位于【地址】，早起的鸟儿有虫吃，对吧
        $earliest         = db('app_user_order_clock_in')
            ->field('clock_in_at-UNIX_TIMESTAMP(FROM_UNIXTIME(clock_in_at,"%Y-%m-%d 00:00:00")) as l,FROM_UNIXTIME(clock_in_at,"%Y-%m-%d %H:%i:%s") as clock_in_at,address')
            ->where('app_user_order_clock_in.created_at', '>=', 1672502400)
            ->where('app_user_order_clock_in.user_id', $param['id'])
            ->where('app_user_order_clock_in.type', 11)
            ->order('l asc')
            ->select();
        $Latest           = db('app_user_order_clock_in')
            ->field('clock_in_at-UNIX_TIMESTAMP(FROM_UNIXTIME(clock_in_at,"%Y-%m-%d 00:00:00")) as l,FROM_UNIXTIME(clock_in_at,"%Y-%m-%d %H:%i:%s") as clock_in_at,address')
            ->where('app_user_order_clock_in.created_at', '>=', 1672502400)
            ->where('app_user_order_clock_in.user_id', $param['id'])
            ->where('app_user_order_clock_in.type', 21)
            ->order('l desc')
            ->select();
        $user['earliest'] = ["l"=>0, "clock_in_at"=>"", "address"=>""];
        $user['Latest']   = ["l"=>0, "clock_in_at"=>"", "address"=>""];
        if (!empty($earliest)) {
            $user['earliest'] = $earliest[0];
            
        }
        if (!empty($Latest)) {
            $user['Latest'] = $Latest[0];
        }
        $user['url'] = 'http://myear.yiniao.cc/#/?type=1&i='.$param['i'].'&city='.config('city');
        res_date($user);
    }
    
    public function StoreManager()
    {
        $param = Request::instance()->get();
        $id = base64_decode(strtr($param['i'], '-_', '+/'));
        $key128 = '0123456789abcdef';
        $iv = 'abcdef0123456789';
        $param['id'] = openssl_decrypt($id, 'aes-128-cbc', $key128, OPENSSL_RAW_DATA, $iv);
        $user  = [];
        //你入职益鸟已经X天了（注解：按照系统内入职时间计算，部分人可能和实际有差异）
        $days_diff     = \db('user')
            ->field('TIMESTAMPDIFF(DAY, FROM_UNIXTIME(created_time,"%Y-%m-%d %H:%i:%s"),NOW()) AS days_diff')
            ->where('user.user_id', $param['id'])->find();
        if(empty($days_diff)){
            res_date(null,300,'分享链接参数错误，无法查看，祝您新年新气象');
        }
        $user['entry'] = empty($days_diff) ? '' : $days_diff['days_diff'];
        //2、过去一年，你签约了X个订单，其中【局部改造】最多，达到X个
        $orderProId = db('order')
            ->field('count(order.pro_id) value,goods_category.title as name')
            ->join('order_times', 'order_times.order_id=order.order_id', 'left')
            ->join('goods_category', 'order.pro_id=goods_category.id', 'left')
            ->where('order.assignor', $param['id'])
            ->where('order_times.signing_time', '>=', 1672502400)
            ->group('order.pro_id')
            ->select();
        
        $user['participateInConstruction'] = empty($orderProId) ? ['count' => 0, 'max' =>['value'=>0,'name'=>''],'list'=>[]] : ['count' => array_sum(array_column($orderProId, 'value')), 'max' => $orderProId[array_search(max(array_column($orderProId, 'value')), array_column($orderProId, 'count'))],'list'=>$orderProId];
        //过去一年，你完工结算了X个订单，最长的一个订单，经历了N天
        $orderPrice         = db('order')
            ->field('TIMESTAMPDIFF(DAY, FROM_UNIXTIME(order.start_time,"%Y-%m-%d %H:%i:%s"),FROM_UNIXTIME(order.finish_time,"%Y-%m-%d %H:%i:%s")) AS gong,order.order_id')
            ->where('order.start_time', '>=', 1672502400)
            ->where('order.finish_time', '>', 0)
            ->where('order.cleared_time', ">", 0)
            ->where('order.assignor', $param['id'])
            ->select();
        $orderConut         = db('order')
            ->field('sum(if(order.state=7 AND ( CASE  WHEN order_aggregate.main_price=0 and order_aggregate.agent_price !=0 and order.settlement_time is Not null THEN 1 WHEN order_aggregate.main_price !=0 and order_aggregate.agent_price =0 and order.cleared_time is not null THEN  1 WHEN order_aggregate.main_price !=0 and order_aggregate.agent_price !=0 and order.cleared_time is not null and order.settlement_time is not null THEN 1  ELSE 0 END)=1,1,0)) as closed')
            ->join('order_times', 'order.order_id=order_times.order_id', 'left')
            ->join('order_aggregate', 'order.order_id=order_aggregate.order_id', 'left')
            ->where('order.assignor', $param['id'])
            ->select();
        $user['orderConut'] = ['count' => empty($orderConut[0]['closed']) ? 0 : $orderConut[0]['closed'], 'maxArray' => empty($orderPrice) ?0 : $orderPrice[array_search(max(array_column($orderPrice, 'gong')), array_column($orderPrice, 'gong'))]['gong']];
        //还记得吗，你最早的一次上门打卡，是X点，位于【地址】，早起的鸟儿有虫吃，对吧
        // 还记得吗，你最晚的一次上门打卡，是X点，位于【地址】，致敬自己，奋斗者
        $earliest            = db('clock_in')
            ->field('punch_clock-UNIX_TIMESTAMP(FROM_UNIXTIME(punch_clock,"%Y-%m-%d 00:00:00")) as l,FROM_UNIXTIME(punch_clock,"%Y-%m-%d %H:%i:%s") as clock_in_at,address')
            ->join('order', 'order.order_id=clock_in.order_id', 'left')
            ->where('clock_in.punch_clock', '>=', 1672502400)
            ->where('order.assignor', $param['id'])
            ->order('l asc')
            ->select();
        $Latest              = db('clock_in')
            ->field('punch_clock-UNIX_TIMESTAMP(FROM_UNIXTIME(punch_clock,"%Y-%m-%d 00:00:00")) as l,FROM_UNIXTIME(punch_clock,"%Y-%m-%d %H:%i:%s") as clock_in_at,address')
            ->join('order', 'order.order_id=clock_in.order_id', 'left')
            ->where('clock_in.punch_clock', '>=', 1672502400)
            ->where('order.assignor', $param['id'])
            ->order('l desc')
            ->select();
        $user['clock_in_at'] = ['earliest' => empty($earliest) ? ["l"=>0, "clock_in_at"=>'', "address"=>""] : $earliest[0], 'Latest' => empty($Latest) ? ["l"=>0, "clock_in_at"=>'', "address"=>""] : $Latest[0]];
        //还记得吗，在X时间，你和客户达成最终协议，完成了合同签署并且收取首笔款，时间很晚了，但你依然斗志昂扬
        $contractLatest = db('contract')
            ->field('con_time,order_id')
            ->join('order', 'order.order_id=contract.orders_id', 'left')
            ->where('contract.con_time', '>=', 1672502400)
            ->order('con_time asc')
            ->where('order.assignor', $param['id'])
            ->select();
        
        $al  = [];
        $al1 = [];
        foreach ($contractLatest as $k => $item) {
            $data   = date('Y-m-d', $item['con_time'] - 86400);
            $datas  = date('Y-m-d 00:00:00', $item['con_time'] - 86400);
            $date   = strtotime(date('Y-m-d 23:59:59', strtotime($data))) + 21600;
            $start  = strtotime(date('Y-m-d 06:00:00', strtotime($data)));
            $data1  = date('Y-m-d', $item['con_time']);
            $date1  = strtotime(date('Y-m-d 23:59:59', strtotime($data1))) + 21600;
            $start1 = strtotime(date('Y-m-d 06:00:00', strtotime($data1)));
            if ($date > $item['con_time'] && $start < $item['con_time']) {
                $contractLatest[$k]['data'] = $data;
            }
            if ($date1 > $item['con_time'] && $start1 < $item['con_time']) {
                $contractLatest[$k]['data'] = $data1;
            }
            $contractLatest[$k]['s']         = abs(strtotime($contractLatest[$k]['data']) - $item['con_time']);
            $contractLatest[$k]['con_times'] = date('Y-m-d H:i:s', $item['con_time']);
        }
        $user['contractLatest'] = empty($contractLatest) ? ["con_time"=>'', "order_id"=>'', "data"=> "", "s"=> 0, "con_times"=>""] : $contractLatest[array_search(max(array_column($contractLatest, 's')), array_column($contractLatest, 's'))];
        //还记得吗，在X时间，你完成了今年最大的一笔签约，金额合计X元
        $orderSigning         = db('order')
            ->field('sum(order_achievement.assert_money) as assert_money,FROM_UNIXTIME(order_achievement.assert_time,"%Y-%m-%d %H:%i:%s") as assert_time')
            ->join('order_achievement', 'order_achievement.order_id=order.order_id', 'left')
            ->where('order.assignor', $param['id'])
            ->where('order_achievement.assert_time', '>=', 1672502400)
            ->where('order_achievement.delete_time', 0)
            ->group('order.order_id')
            ->whereIn('order_achievement.achievement_type', [1, 2])
            ->select();
        $user['orderSigning'] = empty($orderSigning) ? ["assert_money"=> "0", "assert_time"=>""] : $orderSigning[array_search(max(array_column($orderSigning, 'assert_money')), array_column($orderSigning, 'assert_money'))];
        //在过去的2023年，你共计获得销冠X次，所在店铺也获得了X次最高业绩店铺，你的贡献大家都看在眼里（如果没有销冠数据则销冠话术变为：你还没有获得过销冠；如果店铺没有过全国最高业绩则话术变为：你所在的店铺还没有问鼎月度最高业绩店铺，要一起加油哦）
        
        $userFind = \db('store', config('database.zong'))->join('user','user.store_id=store.store_id')->where('user_id',$param['id'])->field('user_id,username,mobile,user_id,store_name,user.store_id')->find();
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $so  = $csv->inputCsv("/www/wwwroot/api.shopowner.yiniao.cc/2023.1-2023.11销冠个人及销冠店铺.csv", '奖项名称,月份,城市,姓名,业绩,备注,电话', '');
        $s1  = $csv->inputCsv("/www/wwwroot/api.shopowner.yiniao.cc/2023.1-2023.11销冠店铺.csv", '奖项名称,月份,城市,姓名,业绩', '');
        $userList=[];
        $storeList=[];
        
        foreach ($so as $k=>$value) {
            if ($userFind['mobile'] == $value["电话"]) {
                $userList[$k]['ye'][] = $value;
                $userList[$k]['user_id'] = $userFind['user_id'];
                $userList[$k]['username'] = $userFind['username'];
            }
        }
        
        
        foreach ($s1 as $k=>$value) {
            if ($userFind['store_name'] == $value["城市"]) {
                $storeList[$k]['ye'][] = $value;
                $storeList[$k]['store_id'] = $userFind['store_id'];
                $storeList[$k]['store_name'] = $userFind['store_name'];
            }
        }
        $userList=array_merge($userList);
        $storeList=array_merge($storeList);
        $user['sellingCrown']=['personaSellingCrown'=>empty($userList)?"0":count($userList),'storeSellingCrown'=>empty($storeList)?0:count($storeList),'type'=>empty($userList)?0:1];
        //经过2023的努力，你总共完成业绩X元，如果非要排名，可能属于【摘星星的人】【只差一点，你就摘到了星星】【没挂科，61分选手】【不多不少，59分】【再努力努力，你又不差】。只是按照全年业绩做的参考，入职时间较短的同学不要灰心哦
        $orderPerformance = db('order', config('database.zong'))
            ->field('sum(order_achievement.assert_money) as total_price,order.assignor,group_concat(order.order_id)')
            ->join('order_achievement', 'order_achievement.order_id=order.order_id', 'left')
            ->join('user', 'user.user_id=order.assignor', 'left')
            ->where('order_achievement.assert_time', '>=', 1672502400)
            ->where('order_achievement.delete_time', 0)
            ->where('user.ce', 1)
            ->group('order.assignor')
            ->order('total_price desc')
            ->select();
        $userList         = [];
        foreach ($orderPerformance as $k => $value) {
            if ($value['assignor'] == $param['id']) {
                $zan   = (string)round(($k + 1) / count($orderPerformance), 3) * 100;
                $title = '';
                if ($zan <= 10) {
                    $title = "【摘星星的人】";
                } elseif (10 <= $zan && 20 >= $zan) {
                    $title = "【只差一点，你就摘到了星星】";
                } elseif (20 <= $zan && 40 >= $zan) {
                    $title = "【没挂科，61分选手】";
                } elseif (40 <= $zan && 50 >= $zan) {
                    $title = "【不多不少，59分】";
                } else {
                    $title = "【再努力努力，你又不差】";
                }
                $userList = [
                    'yeji' => $value['total_price'],
                    'xu' => $k + 1,
                    'zan' => $zan,
                    'title' => $title,
                ];
                break;
            }
        }
        $user['ye'] = empty($userList)?["yeji"=>0, "xu"=> 0, "zan"=>0, "title"=>'【再努力努力，你又不差】']:$userList;
        $user['url'] = 'http://syear.yiniao.cc/#/?type=1&i='.$param['i'].'&city='.config('city');
        res_date($user);
    }
    public function kl(){
        $list=db('shopwner_profit_margin_limitation', config('database.statistics'))->where('id',63)->find();
        $store_id=db('order', config('database.zong'))->join('user','user.user_id=order.assignor','left')->join('store','store.store_id=user.store_id','left')->where('order_id',$list['order_id'])->field('store.store_id,store.city')->find();
        $city = $store_id['city'];
        $give_money=0;
        $expense=0;
        $purchasing_discount=0;
        $purchasing_expense=0;
        if($list['type']==1){
            $give_money=$list['give_money'];
            $expense=$list['expense'];
        }
        if($list['type']==2){
            $purchasing_discount=$list['give_money'];
            $purchasing_expense=$list['expense'];
        }
        $capitalList=json_decode($list['param'],true);
        $l=[];
        foreach ($capitalList as $k => $item) {
            $capitalList[$k]['give_money']          = $give_money;
            $capitalList[$k]['expense']             = $expense;
            $capitalList[$k]['purchasing_discount'] = $purchasing_discount;
            $capitalList[$k]['purchasing_expense']  = $purchasing_expense;
            $capitalList[$k]['created_time']        = time();
            $capitalList[$k]['main_price']          = 0;
            $capitalList[$k]['agent_price']         = 0;
            $option_value_id                        = 0;
            if (isset($item['specsList']) && !empty($item['specsList'])) {
                $option_value_id = array_column($item['specsList'], 'option_value_id');
            }
            $specsList                  = \db('detailed_option_value_material')
                ->field("group_concat(detailed_option_value_material.invcode) as invcode,
                  CONCAT('[', GROUP_CONCAT(JSON_OBJECT('invcode',detailed_option_value_material.invcode,'option_value_id',detailed_option_value_material.option_value_id,'recommended_quantity',detailed_option_value_material.recommended_quantity,'sum',0) SEPARATOR ','), ']') as  content")
                ->whereIn('detailed_option_value_material.option_value_id', $option_value_id)
                ->find();
            $capitalList[$k]['invcode'] = empty($specsList) ? '' : $specsList['invcode'];//三
            $capitalList[$k]['content'] = empty($specsList) ? '' : $specsList['content'];//三
            if (($item['agency'] == 1 && $item['products'] != 1) || ($item['products'] == 1 && $item['unique_status'] && $item['agency'] == 1)) {
                $capitalList[$k]['agent_price']     = $item['toPrice'];
                $capitalList[$k]['daiMaterialCost'] = round($item['base_cost'] * $item['fang'], 2);
                $capitalList[$k]['daiLaborCost']    = $item['labor_cost_price'];
            }
            if (($item['agency'] == 0 && $item['products'] != 1) || ($item['products'] == 1 && $item['unique_status'] && $item['agency'] == 0)) {
                $capitalList[$k]['main_price']   = $item['toPrice'];
                $capitalList[$k]['zhuLaborCost'] = (string)round($item['sumMast'] * $item['fang'], 2);//三
            }
            
            $capitalList[$k]['order_fee'] = 0;
        }
        
        $u8c_invprice      = db('u8c_virtual_material', config('database.zong'))->where(function ($query) use ($store_id) {
            $query->where('store_id', $store_id['store_id'])
                ->whereOr(function ($query) use ($store_id) {
                    $query->where('city_id', $store_id['city'])
                        ->where('store_id', 0);
                });
        })->select();
        $labor_cost_config = db('labor_cost_config', config('database.zong'))->where('city_id', $city)->find();
        $interes_rate      = Db::connect(config('database.zong'))->table('sys_config')->where('keyss', 'interes_rate')->value('valuess');
        $interes_rate      = json_decode($interes_rate, true);
        
        $l                 = $capitalList;
        $s=[];
        foreach ($l as $k => $v) {
            $zhuMaterialCost = 0;
            if (!empty($v['content'])) {
                $content = json_decode($v['content'], true);
                foreach ($content as $item) {
                    foreach ($u8c_invprice as $value) {
                        if ($item['invcode'] == $value['invcode'] && $item['sum'] == 0) {
                            $content[$k]['sum'] = bcmul(bcmul($item['recommended_quantity'], $value['nabprice'], 2), $v['fang'], 2);
                            $zhuMaterialCost    += $content[$k]['sum'];
                            
                        }
                    }
                    
                }
                $v['content'] = json_encode($content);
            }
            if($list['type']==1 && $v['agency'] == 0){
                $s[$k]['order_id']     = $list['order_id'];
                $s[$k]['type']     = $list['type'];
                $s[$k]['order_fee']     = $v['order_fee'];
                $s[$k]['projectTitle']=$v['projectTitle'];
                $s[$k]['categoryTitle']=$v['categoryTitle'];
                $s[$k]['agencyName']='主材';
                if($v['agency'] == 1){
                    $s[$k]['agencyName']='代购';
                }
                $s[$k]['price']     = $v['toPrice'];
                if (($v['agency'] == 1 && $v['products'] != 1) || ($v['products'] == 1 && $v['unique_status'] && $v['agency'] == 1)) {
                    $s[$k]['MaterialCost'] = round($v['base_cost'] * $v['fang'], 2);
                    $s[$k]['LaborCost']    = $v['labor_cost_price'];
                }
                if (($v['agency'] == 0 && $v['products'] != 1) || ($v['products'] == 1 && $v['unique_status'] && $v['agency'] == 0)) {
                    $s[$k]['MaterialCost']=$zhuMaterialCost;
                    $s[$k]['LaborCost'] = (string)round($v['sumMast'] * $v['fang'], 2);//三
                }
                $s[$k]['small_order_fee']=$v['small_order_fee'];
                $s[$k]['give_money']=$list['give_money'];
                $s[$k]['expense']=$list['expense'];
                $s[$k]['order_rate']=$list['order_rate'];
                $s[$k]['exceeding_rate']=$list['exceeding_rate'];
            }
            if($list['type']==2 && $v['agency'] == 1){
                $s[$k]['order_id']     = $list['order_id'];
                $s[$k]['type']     = $list['type'];
                $s[$k]['order_fee']     = $v['order_fee'];
                $s[$k]['projectTitle']=$v['projectTitle'];
                $s[$k]['categoryTitle']=$v['categoryTitle'];
                $s[$k]['agencyName']='主材';
                if($v['agency'] == 1){
                    $s[$k]['agencyName']='代购';
                }
                $s[$k]['price']     = $v['toPrice'];
                if (($v['agency'] == 1 && $v['products'] != 1) || ($v['products'] == 1 && $v['unique_status'] && $v['agency'] == 1)) {
                    $s[$k]['MaterialCost'] = round($v['base_cost'] * $v['fang'], 2);
                    $s[$k]['LaborCost']    = $v['labor_cost_price'];
                }
                if (($v['agency'] == 0 && $v['products'] != 1) || ($v['products'] == 1 && $v['unique_status'] && $v['agency'] == 0)) {
                    $s[$k]['MaterialCost']=$zhuMaterialCost;
                    $s[$k]['LaborCost'] = (string)round($v['sumMast'] * $v['fang'], 2);//三
                }
                $s[$k]['small_order_fee']=$v['small_order_fee'];
                $s[$k]['give_money']=$list['give_money'];
                $s[$k]['expense']=$list['expense'];
                $s[$k]['order_rate']=$list['order_rate'];
                $s[$k]['exceeding_rate']=$list['exceeding_rate'];
            }
            if($list['type']==3){
                $s[$k]['order_id']     = $list['order_id'];
                $s[$k]['type']     = $list['type'];
                $s[$k]['order_fee']     = $v['order_fee'];
                $s[$k]['projectTitle']=$v['projectTitle'];
                $s[$k]['categoryTitle']=$v['categoryTitle'];
                $s[$k]['agencyName']='主材';
                if($v['agency'] == 1){
                    $s[$k]['agencyName']='代购';
                }
                $s[$k]['price']     = $v['toPrice'];
                if (($v['agency'] == 1 && $v['products'] != 1) || ($v['products'] == 1 && $v['unique_status'] && $v['agency'] == 1)) {
                    $s[$k]['MaterialCost'] = round($v['base_cost'] * $v['fang'], 2);
                    $s[$k]['LaborCost']    = $v['labor_cost_price'];
                }
                if (($v['agency'] == 0 && $v['products'] != 1) || ($v['products'] == 1 && $v['unique_status'] && $v['agency'] == 0)) {
                    $s[$k]['MaterialCost']=$zhuMaterialCost;
                    $s[$k]['LaborCost'] = (string)round($v['sumMast'] * $v['fang'], 2);//三
                }
                $s[$k]['small_order_fee']=$v['small_order_fee'];
                $s[$k]['give_money']=$list['give_money'];
                $s[$k]['expense']=$list['expense'];
                $s[$k]['order_rate']=$list['order_rate'];
                $s[$k]['exceeding_rate']=$list['exceeding_rate'];
            }
            
        }
        $xlsCell = ['订单ID', '1主合同2代购3综合', '订单费','项目名称','分组名称','主材/代购','价格','材料费','人工费','小单费','优惠','管理费','订单利润','利润率超出率'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($s, $xlsCell);
        res_date($s);
    }
    public function ki(){
        $store_id=db('capital')->join('order','capital.ordesr_id=order.order_id','left')->join('order_info','order_info.order_id=order.order_id','left')->join('detailed','detailed.detailed_id=capital.projectId','left')->where('capital.types',1)->where('capital.enable',1)->field('order_info.city,order_info.assignor,order.order_no,detailed.detailed_title,capital.projectId,capital.class_b,capital.un_Price,capital.square,capital.to_price,capital.labor_cost')->whereBetween('capital.crtime',[1672502400,1719763199])
            ->where('capital.class_b <> detailed.detailed_title')->where('order.state','<>',10)->select();
        $xlsCell = ['城市', '店长', '订单号','清单ID','分组名称','修改后清单名称','单价','方量','总价','清单结算工费'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($store_id, $xlsCell);
        
    }
    public function rework_end(){
        $rework_end=db('rework_end')->join('order','rework_end.order_id=order.order_id','left')->field('order.assignor,rework_end.update_time,order.deliverer,rework_end.id')->select();
        foreach ($rework_end as $k=>$i){
            $list['user_id']=$i['assignor'];
            if($i['deliverer'] !=0){
                $list['user_id']=$i['deliverer'];
            }
            db('rework_end')->where('id',$i['id'])->update($list);
        }
    }
    public function advertisement(){
        $data = Request::instance()->post();
        $advertisement_count=db('advertisement_count',config('database.zong'))->where(['role'=>$data['role'],'user_id'=>$data['userId'],'ad_id'=>$data['ad_id']])->find();
        $id=$advertisement_count['id'];
        if(empty($advertisement_count)){
            $advertisement_count=db('advertisement_count',config('database.zong'))->insertGetId(['role'=>$data['role'],'user_id'=>$data['userId'],'user_name'=>$data['userName'],'creation_time'=>time(),'ad_id'=>$data['ad_id'],'view_count'=>0]);
            $id=$advertisement_count;
        }
        db('advertisement_click_log',config('database.zong'))->insert(['ad_id'=>$id,'role'=>$data['role'],'user_id'=>$data['userId'],'creation_time'=>time()]);
        db('advertisement_count',config('database.zong'))->where(['id'=>$id])->setInc('view_count',1);
        res_date(null,20);
    }
    public function SignUp()
    {
        $order   = \db('agency_task')
        ->field('agency.agency_id,agency.agency_name,capital.capital_id,capital.class_b,capital.projectId,agency_task.agency_order_id')
        ->join('capital','capital.capital_id=agency_task.capital_id','left')
        ->join('agency','agency.agency_id=agency_task.agency_id','left')
        ->where('agency.user_id','>',0)
        ->where('agency_task.delete_time',0)
        ->select();
        $lk=[];
        foreach ($order as $k=>$i){
            $db=Common::city($i['capital_id']);
            $detailed_option_value_material = Db::connect(config('database.' . $db['db']))->table('detailed')
           ->where('detailed_id',$i['projectId'])
           ->where('user_id','>',0)
            ->find();
            if(!empty($detailed_option_value_material)){
                $lk[$k]['agency_id']=$i['agency_id'];
                $lk[$k]['agency_name']=$i['agency_name'];
                $lk[$k]['capital_id']=$i['capital_id'];
                $lk[$k]['class_b']=$i['class_b'];
                $lk[$k]['projectId']=$i['projectId'];
                $lk[$k]['agency_order_id']=$i['agency_order_id'];
                $lk[$k]['title']=$detailed_option_value_material['detailed_title'];
            }
           
        }
    $xlsCell = ['供应商ID', '供应商名称', '基建ID','基建名称','清单ID','主表ID','清单名称'];
    vendor('phpcvs.Csv');
    $lk=array_merge($lk);
    $csv = new Csv();
    $csv->export($lk, $xlsCell);
    }
    public function SignUp1()
    {
        $order   = \db('order_collection',config('database.zong'))
        ->field('order.order_no,order_info.assignor,order_times.signing_time,order.start_time,order.state,order.finish_time,content_id,result_id')
        ->join('order','order.order_id=order_collection.order_id','left')
        ->join('order_info','order_info.order_id=order_collection.order_id','left')
        ->join('order_times','order_times.order_id=order_collection.order_id','left')
       
        ->select();
        $sys_config  = Db::connect(config('database.zong'))->table('sys_config')->where('keyss', 'order_collection')->value('valuess');
        $sys_config  = json_decode($sys_config, true);
        $lk=[];
        foreach ($order as $k=>$i){
            $lk[$k]['order_no']=$i['order_no'];  
            $lk[$k]['assignor']=$i['assignor'];  
            $lk[$k]['signing_time']=date("Y-m-d H:i:s",$i['signing_time']);  
            $lk[$k]['start_time']=date("Y-m-d H:i:s",$i['start_time']); 
            foreach ($sys_config as $k2=>$i2){
                if($i2['id'] == $i['content_id']){
                    $lk[$k]['title']=$i2['title'];  
                }
                foreach ($i2['select'] as $k3=>$i3){
                    if($i3['id'] == $i['result_id']){
                        $lk[$k]['title1']=$i3['title'];  
                    }
                }
            }
            $lk[$k]['state']=$i['state']; 
            $lk[$k]['finish_time']=date("Y-m-d H:i:s",$i['finish_time']);  
           
        }
    $xlsCell = ['订单号', '店长名称', '签约时间','开工时间','状态','完工时间','选择','选择1','选择','选择1','选择','选择1','选择','选择1'];
    vendor('phpcvs.Csv');
    $lk=array_merge($lk);
    $csv = new Csv();
    $csv->export($lk, $xlsCell);
    }
    public function startupNewandover()
    {
        $cd = db('startup_new_handover')
            ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
            ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
            ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
          ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
            ->select();
        $cq = db('startup_new_handover', config('database.cq'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
        ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        $gy = db('startup_new_handover', config('database.gy'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        
        $wh = db('startup_new_handover', config('database.wh'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        $sz = db('startup_new_handover', config('database.sz'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        $sh = db('startup_new_handover', config('database.sh'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        $bj = db('startup_new_handover', config('database.bj'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        $gz = db('startup_new_handover', config('database.gz'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        
        $fs   = db('startup_new_handover', config('database.fs'))
        ->Join(config('database.connections.zong')['database'] . '.order_info', 'order_info.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order', 'order.order_id=startup_new_handover.order_id', 'left')
        ->Join(config('database.connections.zong')['database'] . '.order_times', 'order_times.order_id=startup_new_handover.order_id', 'left')
      ->field('order.order_no,order_info.assignor,FROM_UNIXTIME(order_times.signing_time,"%Y年%m月%d %H:%i:%S") as uptime,order.state')
        ->select();
        $list = array_merge($cd, $cq, $wh, $gy, $sh, $bj, $sz, $fs, $gz);
        
        $xlsCell = ['订单号', '店长', '签约时间', '状态'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($list, $xlsCell);
    }
    /**订单号，店铺，店长
     * 【最终报价有多个分组】且【交底有多个分组均上传照片】且【已开工】的订单数：
     *【报价有多个分组】但【交底只有一个分组】且【已开工】的订单数：
     */
    public function grouping(){
        $cd=db('order', config('database.cd'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $cdList=[];
        foreach($cd as $K=> $item){
           $categoryName= db('capital', config('database.cd'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.cd'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $cdList[$K]['order_no']=$item['order_no'];
           $cdList[$K]['assignor']=$item['assignor'];
           $cdList[$K]['store_name']=$item['store_name'];
           $cdList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $cdList[$K]['fen']=$categoryName;
           $cdList[$K]['jiaoImg']=$images;
        }
        $cq=db('order', config('database.cq'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $cqList=[];
        foreach($cq as $K=> $item){
           $categoryName= db('capital', config('database.cq'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.cq'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $cqList[$K]['order_no']=$item['order_no'];
           $cqList[$K]['assignor']=$item['assignor'];
           $cqList[$K]['store_name']=$item['store_name'];
           $cqList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $cqList[$K]['fen']=$categoryName;
           $cqList[$K]['jiaoImg']=$images;
        }
        $gy=db('order', config('database.gy'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $gyList=[];
        foreach($gy as $K=> $item){
           $categoryName= db('capital', config('database.gy'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.gy'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $gyList[$K]['order_no']=$item['order_no'];
           $gyList[$K]['assignor']=$item['assignor'];
           $gyList[$K]['store_name']=$item['store_name'];
           $gyList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $gyList[$K]['fen']=$categoryName;
           $gyList[$K]['jiaoImg']=$images;
        }
        $wh=db('order', config('database.wh'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $whList=[];
        foreach($wh as $K=> $item){
           $categoryName= db('capital', config('database.wh'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.wh'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $whList[$K]['order_no']=$item['order_no'];
           $whList[$K]['assignor']=$item['assignor'];
           $whList[$K]['store_name']=$item['store_name'];
           $whList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $whList[$K]['fen']=$categoryName;
           $whList[$K]['jiaoImg']=$images;
        }
        $sz=db('order', config('database.sz'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $szList=[];
        foreach($sz as $K=> $item){
           $categoryName= db('capital', config('database.sz'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.sz'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $szList[$K]['order_no']=$item['order_no'];
           $szList[$K]['assignor']=$item['assignor'];
           $szList[$K]['store_name']=$item['store_name'];
           $szList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $szList[$K]['fen']=$categoryName;
           $szList[$K]['jiaoImg']=$images;
        }
        $sh=db('order', config('database.sh'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $shList=[];
        foreach($sh as $K=> $item){
           $categoryName= db('capital', config('database.sh'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.sh'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $shList[$K]['order_no']=$item['order_no'];
           $shList[$K]['assignor']=$item['assignor'];
           $shList[$K]['store_name']=$item['store_name'];
           $shList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $shList[$K]['fen']=$categoryName;
           $shList[$K]['jiaoImg']=$images;
        }
        $gz=db('order', config('database.gz'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $gzList=[];
        foreach($gz as $K=> $item){
           $categoryName= db('capital', config('database.gz'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.gz'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $gzList[$K]['order_no']=$item['order_no'];
           $gzList[$K]['assignor']=$item['assignor'];
           $gzList[$K]['store_name']=$item['store_name'];
           $gzList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $gzList[$K]['fen']=$categoryName;
           $gzList[$K]['jiaoImg']=$images;
        }
        $fs=db('order', config('database.fs'))->where('order.start_time','>','3月2')->Join('order_info', 'order_info.order_id=order.order_id', 'left')->field('order.order_no,order_info.assignor,order_info.store_name,order.start_time')->select();
        $fsList=[];
        foreach($fs as $K=> $item){
           $categoryName= db('capital', config('database.fs'))->where('ordesr_id',$item['ordesr_id'])->where('types',1)->where('enable',1)->count('categoryName');
           $images= db('startup_new_handover', config('database.fs'))->where('ordesr_id',$item['ordesr_id'])->where('type',2)->whereNotNull('images')->where('delete_time',0)->count('images');
           $fsList[$K]['order_no']=$item['order_no'];
           $fsList[$K]['assignor']=$item['assignor'];
           $fsList[$K]['store_name']=$item['store_name'];
           $fsList[$K]['kai']=empty($item['start_time'])?"暂无开工时间":date('Y-m-d H:i',$item['start_time']);
           $fsList[$K]['fen']=$categoryName;
           $fsList[$K]['jiaoImg']=$images;
        }
        $list = array_merge($cdList, $cqList, $whList, $gyList, $shList, $szList, $fsList, $gzList);
        
        $xlsCell = ['订单号', '店长', '店铺', '开工时间','报价有多个分组','交底分组'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($list, $xlsCell);

    }
    /**
     * 每个人的积分明细
     * @return void
     */
    public function behavior_score()
    {
        $behavior_score = Db::connect(config('database.zong'))->table('behavior_score')
            ->field('user.username,FROM_UNIXTIME(behavior_score.behavior_time, "%Y-%m-%d %H:%i" ) AS stareTime,behavior_score.behavior_type,behavior_score.behavior_score as score')
            ->join('order', 'order.order_id=behavior_score.order_id', 'left')
            ->join('user', 'user.user_id=behavior_score.user_id', 'left')
            ->where('behavior_score.user_type', 1)->where('behavior_score.delete_time', 0)->select();
        foreach ($behavior_score as $k => $value) {
            $title = self::behaviorTitle($behavior_score[$k]['behavior_type'], 2);

            $behavior_score[$k]['behavior_type'] = $title;
           
        }

        $xlsCell = ['店长', '积分创建时间', '积分项目类型', '积分数量'];
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($behavior_score, $xlsCell);
    }
    public static function behaviorTitle($behavior, $type = 1) {
        switch ($behavior) {
            case 1:
                $title  = "1、设置关键工序排期【行为分】";
                $title1 = "设置关键工序排期【行为分】";
                break;
            case 2:
                $title  = "2、上传图文施工交接【行为分】";
                $title1 = "上传图文施工交接【行为分】";
                break;
            case 3:
                $title  = "3、上传排期内关键工序节点施工内容【行为分】";
                $title1 = "上传排期内关键工序节点施工内容【行为分】";
                break;
            case 4:
                $title  = "4、上传符合标准的完工素材【行为分】";
                $title1 = "上传符合标准的完工素材【行为分】";
                break;
            case 5:
                $title  = "5、上传图文施工交接【行为分】";
                $title1 = "上传图文施工交接";
                break;
            case 6:
                $title  = "6、上传符合标准的完工素材【行为分】";
                $title1 = "上传符合标准的完工素材";
                break;
            case 7:
                $title  = "1、项目用户分享【用户分】";
                $title1 = "项目用户分享【用户分】";
                break;
            case 8:
                $title  = "2、投诉【用户分】";
                $title1 = "投诉【用户分】";
                break;
            case 9:
                $title  = "7、完工验收图片审核通过【行为分】";
                $title1 = "完工验收图片审核通过【行为分】";
                break;
            
            default:
                $title  = "";
                $title1 = "";
        }
        if ($type == 1) {
            return $title;
        } else {
            return $title1;
        }
        
    }
    // 导出包含多部分内容的 Word 文档方法
    public function exportWord($orderId)
    {

        // 1. 查询 capital 表数据（可按需添加查询条件，这里示例查询全部）
        $capitalData = Db::name('capital')
            ->field('class_b as 清单名称, company as 单位, square as 销售方量, base_contract_price as 发包价, qi_rmakes as 销售备注,capital_id,agency')
            ->where('types', 1)
            ->where('enable', 1)

            ->where('ordesr_id', $orderId)
            ->select();
        foreach ($capitalData as $k => $value) {
            $capital_value = Db::name('capital_value')

                ->where('capital_id', $value['capital_id'])

                ->column('title');
            $capitalData[$k]['规格'] = empty($capital_value) ? '' : implode('/', $capital_value);
        }

        // 计算发包成本总计（用于发包价说明表格）
        $totalLaborCost = 0;
        // $totalMaterial = 0;
        $base_contract_price = 0;
        foreach ($capitalData as $item) {
            if ($item['agency'] == 0) {
                $base_contract_price += $item['发包价'];
               
            }
        }
        $totalCost = $totalLaborCost + $base_contract_price;

        // 2. 初始化 PHPWord 对象

        $phpWord = new PhpWord();
        // 3. 添加“销售交付交接：发包价说明”部分
        $tableStyle1 = array(
            'borderSize' => 6,
            'borderColor' => '000000',
            'cellMargin' => 50,
        );
        $section1 = $phpWord->addSection();
        $section1->addText('■ 销售交付交接：发包价说明：', ['name' => '微软雅黑', 'size' => 16, 'bold' => true]);
        $table1 = $section1->addTable($tableStyle1);

        // $table1->setStyle($tableStyle1);
        // 表头
        $table1->addRow();
        $table1->addCell(2000)->addText('清单名称', ['bold' => true]);
        $table1->addCell(1000)->addText('规格', ['bold' => true]);
        $table1->addCell(1000)->addText('单位', ['bold' => true]);
        $table1->addCell(1000)->addText('销售方量', ['bold' => true]);
        
        $table1->addCell(1500)->addText('发包价', ['bold' => true]);
        $table1->addCell(2000)->addText('销售备注', ['bold' => true]);
        // 数据行
        foreach ($capitalData as $row) {
            if ($row['agency'] == 0) {
                $table1->addRow();
                $table1->addCell(2000)->addText($row['清单名称']);
                $table1->addCell(1000)->addText($row['规格']); // 规格字段若表中无对应，可先填空，按需从表中取其他字段
                $table1->addCell(1000)->addText($row['单位']);
                $table1->addCell(1000)->addText($row['销售方量']);
               
                $table1->addCell(1500)->addText($row['发包价']);
                $table1->addCell(2000)->addText($row['销售备注']);
            }
        }
        // 总计行
        $table1->addRow();
        $cell1 = $table1->addCell(3500);
        $cell1->addText("成本总计（元）：{$totalCost}", ['bold' => true]);
        $cell1->getStyle()->setGridSpan(3);
        $table1->addCell(1000)->addText('');
        $table1->addCell(1000)->addText($totalLaborCost, ['bold' => true]);
        $table1->addCell(1500)->addText($base_contract_price, ['bold' => true]);
        $table1->addCell(2000)->addText('');

        // 4. 添加“销售交付交接：清单明细”部分
        $tableStyle2 = array(
            'borderSize' => 6,
            'borderColor' => '000000',
            'cellMargin' => 50,
        );
        $section2 = $phpWord->addSection();
        $section2->addTextBreak(); // 换行分隔
        $section2->addText('■ 销售交付交接：清单明细：', ['name' => '微软雅黑', 'size' => 16, 'bold' => true]);
        $table2 = $section2->addTable($tableStyle2);

        // $table2->setStyle($tableStyle2);
        // 表头
        $table2->addRow();
        $table2->addCell(2000)->addText('清单名称', ['bold' => true]);
        $table2->addCell(1500)->addText('规格', ['bold' => true]);
        $table2->addCell(1000)->addText('单位', ['bold' => true]);
        $table2->addCell(1000)->addText('销售方量', ['bold' => true]);
        $table2->addCell(2000)->addText('备注', ['bold' => true]);
        // 数据行
        foreach ($capitalData as $row) {
            $table2->addRow();
            $table2->addCell(2000)->addText($row['清单名称']);
            $table2->addCell(1500)->addText($row['规格']);
            $table2->addCell(1000)->addText($row['单位']);
            $table2->addCell(1000)->addText($row['销售方量']);
            $table2->addCell(2000)->addText($row['销售备注']);
        }
        $startup = Db::name('startup')
            ->field('order_info.gong, order.order_no, concat(order_info.county,order.addres) as addres, startup.sta_time,startup.up_time')
            ->join('order_info', 'order_info.order_id=startup.orders_id', 'left')
            ->join('order', 'order.order_id=startup.orders_id', 'left')

            ->where('startup.orders_id', $orderId)
            ->find();
        // 5. 添加“销售交付交接：交底细则内容”部分
        $section3 = $phpWord->addSection();
        $section3->addTextBreak();
        $section3->addText('■ 销售交付交接：交底细则内容：', ['name' => '微软雅黑', 'size' => 16, 'bold' => true]);
        // 这里的数据可根据实际从表中查询或固定内容，示例用固定内容演示
        $addres = empty($startup) ? '' : $startup['addres'];
        $order_no = empty($startup) ? '' : $startup['order_no'];
        $gong = empty($startup) ? 0 : $startup['gong'] . '工作日';
        $sta_time = empty($startup) ? '' : date('Y-m-d', $startup['sta_time']);
        $up_time = empty($startup) ? '' : date('Y-m-d', $startup['up_time']);
        $detailContent = [
            '项目地址：' . $addres,
            '订单编号：' . $order_no,
            '项目交付工期：' . $gong,
            '项目开工时间：' . $sta_time,
            '项目完工时间：' . $up_time,
            '交接说明：交付标准、验收标准、施工安全管理及服务标准，请根据益鸟美居标准进行（具体内容在 APP 及公司文件）'
        ];
        foreach ($detailContent as $line) {
            $section3->addText($line, ['name' => '宋体', 'size' => 12]);
        }

        // 6. 生成 Word 文件并下载
        $fileName = '销售交付交接表' . date('YmdHis') . '.docx';
        $tempPath = ROOT_PATH . 'runtime/export/' . $fileName;

        if (!is_dir(ROOT_PATH . 'runtime/export/')) {
            mkdir(ROOT_PATH . 'runtime/export/', 0755, true);
        }

        $objWriter = \PhpOffice\PhpWord\IOFactory::createWriter($phpWord, 'Word2007');
        $objWriter->save($tempPath);

        $this->downloadFile($tempPath, $fileName);
    }

    // 下载文件方法
    private function downloadFile($filePath, $fileName)
    {
        if (file_exists($filePath)) {
            header('Content-Type: application/vnd.openxmlformats-officedocument.wordprocessingml.document');
            header('Content-Disposition: attachment;filename="' . $fileName . '"');
            header('Cache-Control: max-age=0');
            readfile($filePath);
            unlink($filePath);
          
        } else {
              r_date('',300,'文件生成失败，请稍后重试');
        }
    }
    public function exportMasterScore()
    {


        $list = Db::connect(config('database.zong'))->table('app_user')
            ->join('scan_evaluation_score', 'app_user.id=scan_evaluation_score.user_id and scan_evaluation_score.delete_time is null  and scan_evaluation_score.user_type=2', 'left')
            ->join('store', 'store.store_id=app_user.store_id', 'left')
            ->join('city', 'city.city_id=store.city', 'left')
            ->field(
                '
        app_user.id,
        app_user.username,
        store.store_name,
        city.city,
        scan_evaluation_score.types,
        ABS(scan_evaluation_score.score) as total_score1,
       FROM_UNIXTIME(scan_evaluation_score.score_time,"%Y-%m-%d") as score_time'
            )
            // ->group('app_user.id')
            ->where('app_user.test_account', 0)
            ->where('app_user.status', 1)
            ->where('app_user.origin', 1)
            ->where('app_user.checked', 1)
            ->where('app_user.quit_jobs', 0)
         
            ->order('score_time desc')
            ->select();





        // 3. 处理数据
        $typeMap = [
            1 => '上下工打卡',
            2 => '三天日程安排',
            3 => '按标准拍照上传',
            4 => '不拒单',
            5 => '无投诉',
            6 => '施工群发消息',
            7 => '工服穿戴',
            8 => '上下道工序验收',
            9 => '线下抽检',
            10 => '完工验收'
        ];
        foreach ($list as $k => $v) {
            $result[$v['id']][] = ['id' => $v['id'], 'fen' => $v['total_score1']];
        }
        foreach ($result as $k => $lists) {
            $schemeTag['id'] = $k;
            $schemeTag['fen'] = array_sum(array_column($result[$k], 'fen'));
            $schemeTag['data']  = $result[$k];
            $tageList[]         = $schemeTag;
        }
        $date = [];
        foreach ($list as $k => $item) {
            foreach ($tageList as  $value) {
                if ($item['id'] == $value['id']) {
                    $list[$k]['fen'] = $value['fen'];
                }
            }
           
            $date[$k]['username'] = $item['username'];
          
            $date[$k]['store_name'] = $item['store_name'];
            $date[$k]['city'] = $item['city'];
            $date[$k]['types'] = $typeMap[$item['types']] ?? '未知';
            $date[$k]['total_score1'] = $item['total_score1'];
            $date[$k]['fen'] =100-$list[$k]['fen'];
            $date[$k]['score_time'] = $item['score_time'];
            $date[$k]['xin'] = 100;
        }
       
        // 表头
        $exportData = [
            '师傅名称',
             '店铺',
            '城市',
            '积分分类',
            '扣减分数',
            '本月剩余积分',
            '扣分时间',
            '本季度累计分数'
        ];
       
        vendor('phpcvs.Csv');
        $csv = new Csv();
        $csv->export($date, $exportData);
    }
}
